package com.xm_jd.model.dao;

import java.util.List;
import com.xm_jd.model.entitys.ShoopandPVO;
import com.xm_jd.utils.BaseDao;

public class Ht_ShoopListDao {
	BaseDao<ShoopandPVO> dao = new BaseDao<>(ShoopandPVO.class);
	
	public List<ShoopandPVO> goodlistp(){
		String sql = "SELECT * FROM tb_shoop INNER JOIN tb_picture ON tb_picture.picture_shoop_Id=tb_shoop.shoop_Id GROUP BY shoop_Id  ORDER BY shoop_Id ;";
		return dao.selectAll(sql, null);
	}
	
	public List<ShoopandPVO> goodlistp(String lb ,String pp,String g1){
		StringBuffer sql = new StringBuffer("SELECT * FROM tb_shoop INNER JOIN tb_picture "
				+ "ON tb_picture.picture_shoop_Id=tb_shoop.shoop_Id "
				+ "INNER JOIN tb_leibie on tb_leibie.leibie_id=tb_shoop.shoop_Sort INNER JOIN tb_pingpai on tb_pingpai.pingpai_id=tb_shoop.shoop_Brand "
				+ " where 1=1 ");
		if(lb != null && !"".equals(lb)){
			sql.append(" and leibie_id='"+lb+"'");	
		}
		if(pp != null && !"".equals(pp)){
			sql.append("and pingpai_id='"+pp+"'");
		}
		if(g1 !=null && !"".equals(g1)){
			sql.append("and shoop_Putaway='"+g1+"'");
		}
		sql.append("GROUP BY shoop_Id  ORDER BY shoop_Id ;");
		return dao.selectAll(sql.toString(), null);
	}
	
	
	/**
	 * @return
	 * 查询类别表
	 */
	public List<ShoopandPVO> leibie(){
		String sql =" SELECT * FROM tb_leibie WHERE leibie_fujiid='0'";
		return dao.selectAll(sql, null);
	}
	
	/**
	 * @return
	 * 查询品牌表
	 */
	public List<ShoopandPVO> pingpai(){
		String sql ="SELECT *FROM tb_pingpai;";
		return dao.selectAll(sql, null);
	}
	
	/**
	 * @param leiname
	 * @return
	 * 根据类别name查询类别id
	 */
	public List<ShoopandPVO> leiname(String leiname){
		String sql = "SELECT leibie_id FROM tb_leibie WHERE tb_leibie.leibie_name=?;";
		return dao.selectAll(sql,new Object[]{leiname});
	}
	
	/**
	 * @param pname
	 * @return
	 * 根据品牌name 查询品牌id
	 */
	public List<ShoopandPVO> pingname(String pname){
		String sql = "SELECT pingpai_id FROM tb_pingpai WHERE pingpai_name=?;";
		return dao.selectAll(sql,new Object[]{pname});
	}
	
	/**
	 * @param lb
	 * @param pp
	 * @param g1
	 * @return
	 * 获得有多少商品记录数
	 */
	public int getTotalNews(){
		String sql = "SELECT count(*) as count FROM tb_shoop";
		return dao.getCounts(sql);
	}
	
	/**
	 * 获取所有商品列表
	 * @return
	 */
	public List<ShoopandPVO> getNewsList(String lb ,String pp,String g1, int current,int pagesize){
		StringBuffer sql = new StringBuffer("SELECT * FROM tb_shoop INNER JOIN tb_picture "
				+ "ON tb_picture.picture_shoop_Id=tb_shoop.shoop_Id "
				+ "INNER JOIN tb_leibie on tb_leibie.leibie_id=tb_shoop.shoop_Sort INNER JOIN tb_pingpai on tb_pingpai.pingpai_id=tb_shoop.shoop_Brand "
				);
		if(lb != null && !"".equals(lb)){
			sql.append(" and leibie_id='"+lb+"'");	
		}
		if(pp != null && !"".equals(pp)){
			sql.append("and pingpai_id='"+pp+"'");
		}
		if(g1 !=null && !"".equals(g1)){
			sql.append("and shoop_Putaway='"+g1+"'");
		}
		sql.append("GROUP BY shoop_Id  ORDER BY shoop_Id LIMIT ?,? ;");
		//第二,调用BaseDao查询
		return dao.selectAll(sql.toString(), new Object[]{(current-1)*pagesize,pagesize});
	}
}
